import os
import re
from typing import List, Dict, Union

import pandas as pd
import pymysql
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font
from openpyxl.utils import get_column_letter
from pypinyin import pinyin, Style

# 多音字映射表
POLYPHONE_MAP = {
    '曾': ['zeng', 'ceng'],
    '行': ['xing', 'hang'],
    '乐': ['le', 'yue']
}

def get_mysql_connection():
    try:
        connection = pymysql.connect(
            host='172.22.182.44',
            user='root',
            password='Cquptsjzl@2025.xxb',
            database='keyan',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        return connection
    except Exception as e:
        print(f"连接MySQL数据库时出错: {e}")
        return None

def read_from_mysql(query):
    connection = get_mysql_connection()
    if not connection:
        return None

    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchall()
            return result
    except Exception as e:
        print(f"执行MySQL查询时出错: {e}")
        return None
    finally:
        connection.close()

def read_excel_rows(file_path, sheet_name=0):
    try:
        # 读取 Excel 文件
        df = pd.read_excel(file_path, sheet_name=sheet_name,dtype={'email': str})

        df['email'] = df['email'].astype(str)  # 先将所有值转为字符串
        df['email'] = df['email'].replace('nan', '')  # 将字符串"nan"替换为空字符串
        df['email'] = df['email'].replace('NaN', '')  # 处理不同大小写变体
        df['email'] = df['email'].replace('<NA>', '')

        rows = df.values.tolist()

        # rows = [df.columns.tolist()] + df.values.tolist()
        return rows

    except Exception as e:
        print(f"读取 Excel 文件时出错: {e}")
        return None


def split_string_by_semicolon(input_string):
    # 检查输入是否为字符串
    if not isinstance(input_string, str):
        raise TypeError("输入必须是字符串类型")

    # 去除字符串首尾空白字符
    stripped_string = input_string.strip()

    # 如果字符串为空，返回空数组
    if not stripped_string:
        return []

    # 以中文分号"；"为分隔符进行拆分
    split_result = stripped_string.split(";")

    # 去除每个结果项的首尾空白字符
    cleaned_result = [item.strip() for item in split_result if item.strip()]

    return cleaned_result

def load_teachers_from_mysql():
    """加载教师信息，返回工号到姓名的映射"""
    query = "SELECT ZGH, XM FROM t_jzg"
    results = read_from_mysql(query)
    return {item['ZGH']: item['XM'] for item in results} if results else {}

def load_students_from_mysql():
    """加载学生信息，返回学号到姓名的映射"""
    query = "SELECT XH, XM FROM t_yjs"
    results = read_from_mysql(query)
    return {item['XH']: item['XM'] for item in results} if results else {}


def load_teachers_by_email(email):
    """根据邮箱查询教师工号"""
    query = f"SELECT ZGH, XM FROM t_jzg WHERE EMAIL='{email}'"
    results = read_from_mysql(query)
    return results[0] if results else None

def convert_pinyin_to_name(pinyin_list: List[Union[str, Dict]],
                           teacher_map: Dict[str, str],
                           student_map: Dict[str, str]) -> List[Dict]:
    """转换拼音为姓名并添加对应的工号/学号"""
    # 合并姓名列表
    all_names = list(teacher_map.values()) + list(student_map.values())

    # 生成拼音映射
    pinyin_map = build_pinyin_map(all_names)

    results = []
    for item in pinyin_list:
        pinyin_str = item if isinstance(item, str) else item.get('pinyin', '')
        normalized = normalize_pinyin(pinyin_str)
        matched_name = match_pinyin(normalized, pinyin_map)

        # 查找匹配的ID
        teacher_match = [zgh for zgh, name in teacher_map.items() if name == matched_name]
        student_match = [xh for xh, name in student_map.items() if name == matched_name]

        result = {
            'pinyin': pinyin_str,
            'name': matched_name or "未匹配",
            'teacher_id': teacher_match[0] if teacher_match else '',
            'student_id': student_match[0] if student_match else ''
        }
        results.append(result)
    return results

def build_pinyin_map(names: List[str]) -> Dict[str, str]:
    pinyin_map = {}
    for name in names:
        if not name or not isinstance(name, str):
            continue

        # 生成基础拼音变体
        variants = generate_pinyin_variants(name)

        # 处理多音字变体
        for char in name:
            if char in POLYPHONE_MAP:
                for pron in POLYPHONE_MAP[char]:
                    variants.update(generate_polyphone_variants(name, char, pron))

        # 注册所有变体
        for variant in variants:
            norm_variant = normalize_pinyin(variant)
            if norm_variant not in pinyin_map:
                pinyin_map[norm_variant] = name

    return pinyin_map


def generate_pinyin_variants(name: str) -> set:
    """生成全面的拼音表达变体"""
    variants = set()
    try:
        py = pinyin(name, style=Style.NORMAL)
        if not py:
            return variants

        # 姓氏部分
        last = py[0][0]
        # 名字部分
        first = ''.join([p[0] for p in py[1:]])

        # 1. 标准格式变体
        variants.add(f"{last.capitalize()}, {first.capitalize()}")
        variants.add(f"{last.capitalize()},{first.capitalize()}")
        variants.add(f"{last.capitalize()} {first.capitalize()}")
        variants.add(f"{first.capitalize()} {last.capitalize()}")

        # 2. 连字符变体
        first_parts = '-'.join([p[0].capitalize() for p in py[1:]])
        variants.add(f"{last.capitalize()}, {first_parts}")
        variants.add(f"{first_parts}-{last.capitalize()}")

        # 3. 紧凑格式
        variants.add(f"{last}{first}")
        variants.add(f"{first}{last}")

        # 4. 首字母缩写
        if len(py) > 1:
            initials = ''.join([p[0][0].upper() for p in py])
            variants.add(initials)

    except Exception as e:
        print(f"生成变体出错 {name}: {str(e)}")

    return variants


def generate_polyphone_variants(name: str, char: str, pronunciation: str) -> set:
    """生成多音字特定变体"""
    variants = set()
    try:
        py = pinyin(name, style=Style.NORMAL)
        char_index = name.index(char)

        # 替换多音字发音
        modified_py = py.copy()
        modified_py[char_index] = [pronunciation]

        # 生成变体（类似基础变体逻辑）
        last = modified_py[0][0]
        first = ''.join([p[0] for p in modified_py[1:]])
        variants.add(f"{last.capitalize()}, {first.capitalize()}")
        variants.add(f"{first.capitalize()} {last.capitalize()}")

    except Exception as e:
        print(f"多音字处理出错 {name}: {str(e)}")

    return variants


def normalize_pinyin(pinyin_str: str) -> str:
    if not pinyin_str:
        return ""

    # 统一处理
    normalized = pinyin_str.lower().strip()

    # 移除所有非字母字符（保留逗号）
    normalized = re.sub(r"[^a-z,]", "", normalized)

    # 标准化姓氏和名字顺序
    if "," in normalized:
        parts = [p.strip() for p in normalized.split(",", 1)]
        return f"{parts[0]},{parts[1]}"
    else:
        if len(normalized) >= 2:
            # 尝试识别常见姓氏
            common_surnames = ['zh', 'ch', 'sh', 'li', 'wa', 'ya']
            for i in [2, 1]:
                if normalized[:i] in common_surnames or i == 1:
                    return f"{normalized[:i]},{normalized[i:]}"

    return normalized


def match_pinyin(pinyin_str: str, pinyin_map: Dict[str, str]) -> str:
    # 1. 精确匹配
    normalized = normalize_pinyin(pinyin_str)
    if normalized in pinyin_map:
        return pinyin_map[normalized]

    # 2. 尝试移除所有分隔符
    compact = re.sub(r"[^a-z]", "", normalized)
    if compact in pinyin_map:
        return pinyin_map[compact]

    # 3. 尝试交换姓和名
    # if "," in normalized:
    #     last, first = normalized.split(",", 1)
    #     reversed_norm = f"{first},{last}"
    #     if reversed_norm in pinyin_map:
    #         return pinyin_map[reversed_norm]

    # 4. 首字母匹配（针对缩写）
    if len(pinyin_str) <= 3 and pinyin_str.isupper():
        initials = re.sub(r"[^a-z]", "", normalized)
        for key in pinyin_map:
            if re.sub(r"[^a-z]", "", key) == initials:
                return pinyin_map[key]

    return ""


def remove_duplicate_teachers(teacher_list):
    seen_ids = set()
    unique_teachers = []

    for teacher in teacher_list:
        teacher_id = teacher.get('teacher_id', '')
        # 如果工号为空或已存在则跳过
        if not teacher_id or teacher_id in seen_ids:
            continue

        seen_ids.add(teacher_id)
        unique_teachers.append(teacher)

    return unique_teachers

def match_name(pinyin_result, teacher_map, student_map):
    """匹配教师和学生信息"""
    # 转换拼音为姓名并添加ID
    combined_result = convert_pinyin_to_name(
        pinyin_result, teacher_map, student_map
    )

    # 分离教师和学生
    teacher_result = []
    student_result = []
    unprocessed = []

    for item in combined_result:
        if item['teacher_id']:
            teacher_result.append({
                'teacher_id': item['teacher_id'],
                'teacher_name': item['name']
            })
        elif item['student_id']:
            student_result.append({
                'student_id': item['student_id'],
                'student_name': item['name']
            })
        else:
            unprocessed.append(item['pinyin'])

    return teacher_result, student_result, unprocessed


def process_excel_data(file_path, output_path):
    """处理Excel数据并保存结果"""
    all_rows = read_excel_rows(file_path, 0)
    if not all_rows:
        return

    # 预加载教师和学生数据
    teacher_map = load_teachers_from_mysql()
    student_map = load_students_from_mysql()

    results = []

    for row in all_rows:
        if len(row) < 3:  # 确保行有足够列
            continue

        paper_title = row[0]  # 论文题目
        pinyin_str = row[1]  # 作者拼音
        email_str = row[2]  # 邮箱地址

        # 处理邮箱对应的教师
        email_teacher = None
        if email_str:
            email_list = split_string_by_semicolon(email_str)
            if email_list:
                email_teacher = load_teachers_by_email(email_list[0])

        # 处理拼音列表
        pinyin_list = split_string_by_semicolon(pinyin_str)

        # 匹配姓名
        teachers, students, unprocessed = match_name(
            pinyin_list, teacher_map, student_map
        )

        # 添加邮箱对应的教师
        if email_teacher:
            # 确保邮箱教师不在现有教师列表中
            email_zgh = email_teacher.get('ZGH', '')
            if email_zgh and email_zgh not in [t.get('teacher_id', '') for t in teachers]:
                teachers.append({
                    'teacher_id': email_zgh,
                    'teacher_name': email_teacher.get('XM', '')
                })

        # 删除教师列表中的重复值（基于工号）
        teachers = remove_duplicate_teachers(teachers)

        # 准备结果行
        result_row = {
            '论文题目': paper_title,
            '教师工号': ';'.join([t.get('teacher_id', '') for t in teachers]),
            '教师姓名': ';'.join([t.get('teacher_name', '') for t in teachers]),
            '学生学号': ';'.join([s.get('student_id', '') for s in students]),
            '学生姓名': ';'.join([s.get('student_name', '') for s in students]),
            '未处理名字': ';'.join(unprocessed)
        }
        results.append(result_row)

    # 保存到Excel
    output_df = pd.DataFrame(results)
    write_to_excel(output_df, output_path)


def write_to_excel(df, output_path):
    try:
        # 确保输出目录存在
        output_dir = os.path.dirname(output_path)
        if output_dir and not os.path.exists(output_dir):
            os.makedirs(output_dir)

        # 创建临时Excel文件
        temp_path = output_path.replace('.xlsx', '_temp.xlsx')
        df.to_excel(temp_path, index=False, sheet_name='处理结果')

        # 使用openpyxl添加格式化
        workbook = load_workbook(temp_path)
        sheet = workbook['处理结果']

        # 设置标题格式
        header_fill = PatternFill(start_color='D7E4BC', end_color='D7E4BC', fill_type='solid')
        header_font = Font(bold=True)
        header_alignment = Alignment(horizontal='center', vertical='center')
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )

        # 应用标题格式
        for cell in sheet[1]:
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = header_alignment
            cell.border = thin_border

        # 应用单元格格式
        for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
            for cell in row:
                cell.border = thin_border

        # 自动调整列宽
        for column in sheet.columns:
            max_length = 0
            column_letter = get_column_letter(column[0].column)

            # 获取列中单元格的最大长度
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass

            # 设置列宽（稍大一些以留出空间）
            adjusted_width = (max_length + 2)
            sheet.column_dimensions[column_letter].width = adjusted_width

        # 保存并删除临时文件
        workbook.save(output_path)
        os.remove(temp_path)

        print(f"成功保存结果到: {output_path}")
        return True

    except Exception as e:
        print(f"保存Excel文件时出错: {str(e)}")
        return False

input_file = r"C:\Users\Administrator\Desktop\test_data.xlsx"
output_file = r"C:\Users\Administrator\Desktop\output_result.xlsx"
process_excel_data(input_file, output_file)

